package com.lx.dao.imp;

import com.lx.bean.Express;
import com.lx.dao.ExpressDao;
import com.lx.exception.CodeExitException;
import com.lx.util.DruidUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @ClassName: ExpressDaoImpl
 * @Description:
 * @Date Created in  2021-07-07 9:43
 * @Modified By:
 * @Author: 零巽
 */
public class ExpressDaoImpl implements ExpressDao {
    public static final String SQL_CONSOLE = "SELECT " +
            "COUNT(ID) data1_size," +
            "COUNT(TO_DAYS(INTIME)=TO_DAYS(NOW()) OR NULL) data1_increment," +
            "COUNT(STATUS=0 OR NULL) data2_size," +
            "COUNT(TO_DAYS(INTIME)=TO_DAYS(NOW()) AND STATUS=0 OR NULL) data2_increment" +
            " FROM EXPRESS";
    public static final String SQL_FIND_ALL = "SELECT * FROM EXPRESS";
    public static final String SQL_FIND_LIMIT = "SELECT * FROM EXPRESS LIMIT ?,?";
    public static final String SQL_FIND_BY_CODE = "SELECT * FROM EXPRESS WHERE CODE=?";
    public static final String SQL_FIND_BY_NUMBER = "SELECT * FROM EXPRESS WHERE NUMBER=?";
    public static final String SQL_FIND_BY_SYSPHONE = "SELECT * FROM EXPRESS WHERE SYSPHONE=?";
    public static final String SQL_FIND_BY_USERPHONE = "SELECT * FROM EXPRESS WHERE USERPHONE=?";
    public static final String SQL_ADD = "INSERT INTO EXPRESS (NUMBER,USERNAME,USERPHONE,COMPANY,CODE,INTIME,STATUS,SYSPHONE) VALUES(?,?,?,?,?,NOW(),0,?)";
    public static final String SQL_UPDATE = "UPDATE EXPRESS SET NUMBER=?,USERNAME=?,COMPANY=?,STATUS=? WHERE ID=?";
    public static final String SQL_UPDATE_STATUS = "UPDATE EXPRESS SET STATUS=1,OUTTIME=NOW(),CODE=NULL WHERE CODE=?";
    public static final String SQL_DELETE = "DELETE FROM EXPRESS WHERE ID=?";
    public static final String SQL_COUNT_TOTAL_LAZY_BOARD = "SELECT USERPHONE PHONE,COUNT(STATUS=1 OR NULL) TOTAL FROM EXPRESS GROUP BY USERPHONE ORDER BY TOTAL DESC";
    public static final String SQL_COUNT_YEAR_LAZY_BOARD = "SELECT USERPHONE PHONE,COUNT(TO_DAYS(NOW())-TO_DAYS(OUTTIME)<31 AND STATUS=1 OR NULL) TOTAL FROM EXPRESS GROUP BY USERPHONE ORDER BY TOTAL DESC";
    public static final String SQL_COUNT_MONTH_LAZY_BOARD = "SELECT USERPHONE PHONE,COUNT(TO_DAYS(NOW())-TO_DAYS(OUTTIME)<365 AND STATUS=1 OR NULL) TOTAL FROM EXPRESS GROUP BY USERPHONE ORDER BY TOTAL DESC";


    /**
     * 用于查询全部快递，总数+新增   待取件+新增
     *
     * @return [{size:总数,increment:新增},{size:总数,increment:新增}]
     */
    @Override
    public List<Map<String, Integer>> console() {
        List<Map<String, Integer>> data = new ArrayList<>();
        //1、获取数据库连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        //2、预编译SQL语句
        try {
            preparedStatement = connection.prepareStatement(SQL_CONSOLE);
            //3、填充参数
            //4、执行SQL语句
            resultSet = preparedStatement.executeQuery();
            //5、获取执行结果
            while (resultSet.next()) {
                int data1_size = resultSet.getInt("data1_size");
                int data1_increment = resultSet.getInt("data1_increment");
                int data2_size = resultSet.getInt("data1_size");
                int data2_increment = resultSet.getInt("data2_increment");
                Map<String, Integer> data1 = new HashMap<>();
                data1.put("data1_size", data1_size);
                data1.put("data1_increment", data1_increment);
                Map<String, Integer> data2 = new HashMap<>();
                data2.put("data2_size", data2_size);
                data2.put("data2_increment", data2_increment);
                data.add(data1);
                data.add(data2);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //6、释放资源
            DruidUtil.close(connection, preparedStatement, resultSet);
        }

        return data;
    }

    /**
     * 用于查询所有快递
     *
     * @param limit    是否开启分页，true表示是，false表示否（查询所有）
     * @param start    查询的起始索引
     * @param pageSize 分页的页容量
     * @return 查询到的快递集合
     */
    @Override
    public List<Express> findAll(boolean limit, int start, int pageSize) {
        List<Express> list = new ArrayList<>();
        //1、获取数据库连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        //2、预编译SQL语句
        try {
            if (limit) {
                preparedStatement = connection.prepareStatement(SQL_FIND_LIMIT);
                //3、填充参数
                preparedStatement.setInt(1, start);
                preparedStatement.setInt(2, pageSize);
            } else {
                preparedStatement = connection.prepareStatement(SQL_FIND_ALL);
            }
            //4、执行SQL语句
            resultSet = preparedStatement.executeQuery();
            //5、获取执行结果
            while (resultSet.next()) {
                Express express = new Express();
                express.setId(resultSet.getInt("id"));
                express.setNumber(resultSet.getString("number"));
                express.setCompany(resultSet.getString("company"));
                express.setUserName(resultSet.getString("userName"));
                express.setUserPhone(resultSet.getString("userPhone"));
                express.setSysPhone(resultSet.getString("sysPhone"));
                express.setCode(resultSet.getString("code"));
                express.setStatus(resultSet.getInt("status"));
                express.setInTime(resultSet.getTimestamp("inTime"));
                express.setOutTime(resultSet.getTimestamp("outTime"));
                list.add(express);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //6、释放资源
            DruidUtil.close(connection, preparedStatement, resultSet);
        }
        return list;
    }

    /**
     * 根据快递单号查询快递
     *
     * @param Number 快递单号
     * @return 查询到的快递信息，不存在时返回null
     */
    @Override
    public Express findByNumber(String Number) {
        //1、获取数据库连接
        Express express = new Express();
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        //2、预编译SQL语句
        try {
            preparedStatement = connection.prepareStatement(SQL_FIND_BY_NUMBER);
            //3、填充参数
            preparedStatement.setString(1, Number);
            //4、执行SQL语句
            resultSet = preparedStatement.executeQuery();
            //5、获取执行结果
            if (resultSet.next()) {
                express.setId(resultSet.getInt("id"));
                express.setNumber(resultSet.getString("number"));
                express.setCompany(resultSet.getString("company"));
                express.setUserName(resultSet.getString("userName"));
                express.setUserPhone(resultSet.getString("userPhone"));
                express.setSysPhone(resultSet.getString("sysPhone"));
                express.setCode(resultSet.getString("code"));
                express.setStatus(resultSet.getInt("status"));
                express.setInTime(resultSet.getTimestamp("inTime"));
                express.setOutTime(resultSet.getTimestamp("outTime"));
                return express;
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //6、释放资源
            DruidUtil.close(connection, preparedStatement, resultSet);
        }
        return null;
    }

    /**
     * 根据取件码查询快递
     *
     * @param code 取件码
     * @return 查询到的快递信息，不存在时返回null
     */
    @Override
    public Express findByCode(String code) {
        //1、获取数据库连接
        Express express = new Express();
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        //2、预编译SQL语句
        try {
            preparedStatement = connection.prepareStatement(SQL_FIND_BY_CODE);
            //3、填充参数
            preparedStatement.setString(1, code);
            //4、执行SQL语句
            resultSet = preparedStatement.executeQuery();
            //5、获取执行结果
            if (resultSet.next()) {
                express.setId(resultSet.getInt("id"));
                express.setNumber(resultSet.getString("number"));
                express.setCompany(resultSet.getString("company"));
                express.setUserName(resultSet.getString("userName"));
                express.setUserPhone(resultSet.getString("userPhone"));
                express.setSysPhone(resultSet.getString("sysPhone"));
                express.setCode(resultSet.getString("code"));
                express.setStatus(resultSet.getInt("status"));
                express.setInTime(resultSet.getTimestamp("inTime"));
                express.setOutTime(resultSet.getTimestamp("outTime"));
                return express;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //6、释放资源
            DruidUtil.close(connection, preparedStatement, resultSet);
        }
        return null;
    }

    /**
     * 根据用户手机号码查询快递
     *
     * @param UserPhone 手机号码
     * @return 查询到的快递信息，不存在时返回null
     */
    @Override
    public List<Express> findByUserPhone(String UserPhone) {
        List<Express> list = new ArrayList<>();
        //1、获取数据库连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        //2、预编译SQL语句
        try {
            preparedStatement = connection.prepareStatement(SQL_FIND_BY_USERPHONE);
            //3、填充参数
            preparedStatement.setString(1, UserPhone);
            //4、执行SQL语句
            resultSet = preparedStatement.executeQuery();
            //5、获取执行结果
            while (resultSet.next()) {
                Express express = new Express();
                express.setId(resultSet.getInt("id"));
                express.setNumber(resultSet.getString("number"));
                express.setCompany(resultSet.getString("company"));
                express.setUserName(resultSet.getString("userName"));
                express.setUserPhone(resultSet.getString("userPhone"));
                express.setSysPhone(resultSet.getString("sysPhone"));
                express.setCode(resultSet.getString("code"));
                express.setStatus(resultSet.getInt("status"));
                express.setInTime(resultSet.getTimestamp("inTime"));
                express.setOutTime(resultSet.getTimestamp("outTime"));
                list.add(express);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //6、释放资源
            DruidUtil.close(connection, preparedStatement, resultSet);
        }
        return list;
    }

    /**
     * 根据录入人手机号码查询快递
     *
     * @param SysPhone 手机号码
     * @return 查询到的快递信息，不存在时返回null
     */
    @Override
    public List<Express> findBySysPhone(String SysPhone) {
        List<Express> list = new ArrayList<>();
        //1、获取数据库连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        //2、预编译SQL语句
        try {
            preparedStatement = connection.prepareStatement(SQL_FIND_BY_SYSPHONE);
            //3、填充参数
            preparedStatement.setString(1, SysPhone);
            //4、执行SQL语句
            resultSet = preparedStatement.executeQuery();
            //5、获取执行结果
            while (resultSet.next()) {
                Express express = new Express();
                express.setId(resultSet.getInt("id"));
                express.setNumber(resultSet.getString("number"));
                express.setCompany(resultSet.getString("company"));
                express.setUserName(resultSet.getString("userName"));
                express.setUserPhone(resultSet.getString("userPhone"));
                express.setSysPhone(resultSet.getString("sysPhone"));
                express.setCode(resultSet.getString("code"));
                express.setStatus(resultSet.getInt("status"));
                express.setInTime(resultSet.getTimestamp("inTime"));
                express.setOutTime(resultSet.getTimestamp("outTime"));
                list.add(express);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //6、释放资源
            DruidUtil.close(connection, preparedStatement, resultSet);
        }
        return list;
    }

    /**
     * 快递录入
     *
     * @param express 需要录入的快递
     * @return true表示录入成功，false表示失败
     */
    @Override
    public boolean add(Express express) throws CodeExitException {
        //1、获取数据库连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            //2、预编译SQL语句
            preparedStatement  = connection.prepareStatement(SQL_ADD);
            //3、填充参数
            //INSERT INTO EXPRESS (NUMBER,USERNAME,USERPHONE,COMPANY,CODE,INTIME,STATUS,SYSPHONE) VALUES(?,?,?,?,?,NOW(),0,?)
            preparedStatement.setString(1, express.getNumber());
            preparedStatement.setString(2, express.getUserName());
            preparedStatement.setString(3, express.getUserPhone());
            preparedStatement.setString(4, express.getCompany());
            preparedStatement.setString(5, express.getCode());
            preparedStatement.setString(6, express.getSysPhone());
            //4、执行SQL语句并获取执行结果
            return preparedStatement.executeUpdate() > 0 ? true : false;

        } catch (SQLException throwables) {
//            throwables.printStackTrace();
            //处理取件码重复异常
            String message = throwables.getMessage();
            if (message.endsWith("for key 'express.code'")) {
                //说明出现取件码重复异常
                CodeExitException codeExitException = new CodeExitException(throwables.getMessage());
                throw codeExitException;
            } else {
                throwables.printStackTrace();
            }

        } finally {
            //6、释放资源
            DruidUtil.close(connection,preparedStatement,null);
        }
        return false;
    }

    /**
     * 快递修改
     *
     * @param id      需要修改的快递id
     * @param express 修改后的快递信息
     * @return true表示录入成功，false表示失败
     */
    @Override
    public boolean update(int id, Express express) {
        //1、获取数据库连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        //2、预编译SQL语句
        try {
            preparedStatement = connection.prepareStatement(SQL_UPDATE);
            //3、填充参数
            preparedStatement.setString(1, express.getNumber());
            preparedStatement.setString(2, express.getUserName());
            preparedStatement.setString(3,express.getCompany());
            preparedStatement.setInt(4,express.getStatus());
            preparedStatement.setInt(5,id);
            //4、执行SQL语句获取执行结果
            return preparedStatement.executeUpdate() > 0 ? true : false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5、释放资源
            DruidUtil.close(connection,preparedStatement,null);
        }
        return false;
    }

    /**
     * 通过id删除快递
     *
     * @param id 需要删除的快递id
     * @return true表示删除成功，false表示失败
     */
    @Override
    public boolean deleteById(int id) {
        //1、获取数据库连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        //2、预编译SQL语句
        try {
            preparedStatement = connection.prepareStatement(SQL_DELETE);
            //3、填充参数
            preparedStatement.setInt(1, id);
            //4、执行SQL语句获取执行结果
            return preparedStatement.executeUpdate() > 0 ? true : false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5、释放资源
            DruidUtil.close(connection,preparedStatement,null);
        }
        return false;
    }

    /**
     * 通过取件码更改快递状态（取件）
     *
     * @param code 快递取件码
     * @return true 表示更改成功，false表示失败
     */
    @Override
    public boolean changeStatusByCode(String code) {
        //1、获取数据库连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        //2、预编译SQL语句
        try {
            preparedStatement = connection.prepareStatement(SQL_UPDATE_STATUS);
            //3、填充参数
            preparedStatement.setString(1,code);
            //4、执行SQL语句获取执行结果
            return preparedStatement.executeUpdate() > 0 ? true : false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5、释放资源
            DruidUtil.close(connection,preparedStatement,null);
        }
        return false;
    }

    @Override
    public List[] getTotalBoard() {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(SQL_COUNT_TOTAL_LAZY_BOARD);
            resultSet = preparedStatement.executeQuery();
            List phoneList = new ArrayList();
            List amountList = new ArrayList();
            while (resultSet.next()) {
                String phone = resultSet.getString(1);
                int amount = resultSet.getInt(2);
                phoneList.add(phone);
                amountList.add(amount);
            }
            List[] total = new List[2];
            total[0]=phoneList;
            total[1] = amountList;
            return total;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement,resultSet);
        }
        return null;
    }

    /**
     * 月排行
     *
     * @return 【用户电话集合，用户数组集合】（降序排序）
     */
    @Override
    public List[] getMonthBoard() {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(SQL_COUNT_MONTH_LAZY_BOARD);
            resultSet = preparedStatement.executeQuery();
            List phoneList = new ArrayList();
            List amountList = new ArrayList();
            while (resultSet.next()) {
                String phone = resultSet.getString(1);
                int amount = resultSet.getInt(2);
                phoneList.add(phone);
                amountList.add(amount);
            }
            List[] month = new List[2];
            month[0]=phoneList;
            month[1] = amountList;
            return month;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement,resultSet);
        }
        return null;
    }

    /**
     * 年排行
     *
     * @return 【用户电话集合，用户数组集合】（降序排序）
     */
    @Override
    public List[] getYearBoard() {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(SQL_COUNT_YEAR_LAZY_BOARD);
            resultSet = preparedStatement.executeQuery();
            List phoneList = new ArrayList();
            List amountList = new ArrayList();
            while (resultSet.next()) {
                String phone = resultSet.getString(1);
                int amount = resultSet.getInt(2);
                phoneList.add(phone);
                amountList.add(amount);
            }
            List[] year = new List[2];
            year[0]=phoneList;
            year[1] = amountList;
            return year;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement,resultSet);
        }
        return null;
    }


}
